<?php
class ASSOCIATIONS{
    //general variables
	var $dbErrorMsg = "Failed set/get data from database (Class associations): ";

    /** Function for getting the association
     * @param  $associationId
     * @return array
     */
    function getAssociation($associationId=0){    	
        if($associationId == 0){
        $sql="describe `".tblPrefix."associations`";
    		$tmpres=MAIN::returnArray($sql);
    		$res=array();
    		foreach($tmpres as $r){
    			if($r['Field']=='id'){
    				$res[0][$r['Field']]=0;
    			}else{
    				$res[0][$r['Field']]='';
    			}    			
    		}
        }else{
			$sql="select `".tblPrefix."associations`.*				
				from `".tblPrefix."associations`				 			 
				 where `".tblPrefix."associations`.`id`=".$associationId;
			$res=MAIN::returnArray($sql);
        }
        return $res[0];
    }
	
	/** Function add/update association
	 * @param : posted data in DATA array, association ID
	 * @return: associationId 
	 */
    function setAssociation($id,$DATA){    	
    	$name=mysql_real_escape_string($DATA['txt_association_name']);
    	$org=mysql_real_escape_string($DATA['txt_association_org']);
		$address=mysql_real_escape_string($DATA['txt_association_address']);
		$zip=mysql_real_escape_string($DATA['txt_association_zip']);
		$city=mysql_real_escape_string($DATA['txt_association_city']);		
		$memberOf = mysql_real_escape_string($DATA['ddl_association_member_of']);
		$ownedBy = mysql_real_escape_string(@$DATA['ddl_association_owned_by']);
		$status = mysql_real_escape_string(@$DATA['ddl_association_status']);
    	$sql="select `".tblPrefix."associations`.`id` from `".tblPrefix."associations` where `id`=".$id;
		$res=MAIN::returnArray($sql);
        if(count($res)>0){
        	//edit
        	$sql = "update `".tblPrefix."associations` set 
				`name`='".$name."',`org`='".$org."',`address`='".$address."',`zip`='".$zip."',
				`city`='".$city."',`member_of`='".$memberOf."',`owned_by`='".$ownedBy."',`status`='".$status."',
				`modif`=NOW() where `id`=".$id;
        }else{
        	//insert
        	$sql = "insert into `".tblPrefix."associations` (`name`,`org`,`address`,
										`zip`,`city`,`member_of`,`owned_by`,`created_by`,`status`,`created_on`,`modif`)
								 values ('".$name."','".$org."','".$address."',
								 		'".$zip."','".$city."','".$memberOf."','".$ownedBy."','".MAIN::decode($_SESSION[svar])."',1,NOW(),NOW())";
        }
		MAIN::executeCommand($sql);
		if($id==0){
			$associationId=mysql_insert_id();
			$desc="New ASSOCIATION";
		}else{
			$associationId = $id;
			$desc="ASSOCIATION Update";
		}
		LOGS::setLog('ASSOCIATION', $desc, $associationId, MAIN::getIP());    	
		return $associationId;
    }	
	/** Function returning all associations in database 
	 * @param : order by, order order
	 * @return: array (associations)
	 */
    function getAssociations($orderBy='name',$ord='asc'){
    	$tmpUser = USERS::getUser(-1);    	
    	if($tmpUser['grouplevel']<=30){
    		$sql = "select `".tblPrefix."associations`.*				
		        from `".tblPrefix."associations`		        
		        ORDER BY `".$orderBy."` ".$ord;
    	}else if($tmpUser['grouplevel']==50){
			$sql = "select `".tblPrefix."associations`.*				
			        from `".tblPrefix."associations`
			        WHERE (`".tblPrefix."associations`.`owned_by`=".$tmpUser['id'].")
			        OR (`".tblPrefix."associations`.`created_by`=".$tmpUser['id'].")
			        OR (`".tblPrefix."associations`.`id`=".$tmpUser['association_id'].")
			        ORDER BY `".$orderBy."` ".$ord;
		}else if($tmpUser['grouplevel']>50){
			$sql = "select `".tblPrefix."associations`.*				
			        from `".tblPrefix."associations`
			        WHERE `".tblPrefix."associations`.`id`=".$tmpUser['association_id']."
			        ORDER BY `".$orderBy."` ".$ord;
		}
		return MAIN::returnArray($sql);
	}
	/** Function returning all associations members 
	 * @param : associationId, order by, order order
	 * @return: array (members -- persons)
	 */
	function getAssociationMembers($associationId,$orderBy='lastname',$ord='asc'){
		$sql="select `".tblPrefix."persons`.*,`".tblPrefix."users`.`active`,`".tblPrefix."users`.`email`,(SELECT MAX(`".tblPrefix."logs`.`modif`) FROM `".tblPrefix."logs`								 
						WHERE `".tblPrefix."users`.`id` = `".tblPrefix."logs`.`user_id` 
						AND `".tblPrefix."logs`.`type` = 'LOGIN' 
						AND `".tblPrefix."logs`.`description` = 'success') as last_login  
		from `".tblPrefix."persons`
		LEFT JOIN `".tblPrefix."users` ON `".tblPrefix."persons`.`user_id`=`".tblPrefix."users`.`id`
		where `".tblPrefix."persons`.`association_id`=".$associationId."
		ORDER BY `".$orderBy."` ".$ord;
		return MAIN::returnArray($sql);
	}
	/** Function returning all associations in database 
	 * @param : order by, order order
	 * @return: array (associations)
	 */
    function searchAssociations($str,$orderBy='name',$ord='asc'){
    	$str = mysql_real_escape_string($str);     	    	
		$sql = "select `".tblPrefix."associations`.*
		        from `".tblPrefix."associations`
		        WHERE `".tblPrefix."associations`.`name` LIKE '%".$str."%'
		        ORDER BY `".$orderBy."` ".$ord;    	
		return MAIN::returnArray($sql);
	}
	/**
	 * Function for removing user data from database
	 * @param : associationId
	 * @return: void
	 */
    function delAssociation($associationId){
    	$sql="delete from `".tblPrefix."associations` where `id`=".$associationId;    	
        LOGS::setLog('ASSOCIATION', "ASSOCIATION Delete", $associationId, MAIN::getIP());
		return MAIN::executeCommand($sql);
    }
    /**
	 * Function for returning association's study circles or programs
	 */
	function getAssociationCircles($associationId){
		
		$sql="SELECT `".tblPrefix."associations`.`id`,`".tblPrefix."circles`.* 
		from `".tblPrefix."associations`
		INNER JOIN `".tblPrefix."registrations` on `".tblPrefix."associations`.`id`=`".tblPrefix."registrations`.`association_id`
		INNER JOIN `".tblPrefix."circles` on `".tblPrefix."circles`.`id`=`".tblPrefix."registrations`.`circle_id`
		WHERE `".tblPrefix."associations`.`id`=".$associationId."
		GROUP BY `".tblPrefix."circles`.`id`
		";
		return MAIN::returnArray($sql);
	}
	
}
?>